#!/bin/bash

# 数据库备份脚本 (Linux/macOS)
# Database Backup Script for Delivery Management System

set -e

# 配置
DB_PATH="../data/delivery-management.db"
BACKUP_DIR="./backup"
SCHEMA_DIR="./schema"
DATA_DIR="./data"

# 颜色输出
GREEN='\033[0;32m'
BLUE='\033[0;34m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo -e "${BLUE}=== 交付管理系统数据库备份脚本 ===${NC}"
echo -e "${BLUE}Delivery Management System Database Backup Script${NC}"
echo ""

# 检查数据库文件是否存在
if [ ! -f "$DB_PATH" ]; then
    echo "错误: 数据库文件 $DB_PATH 不存在"
    exit 1
fi

# 检查sqlite3是否安装
if ! command -v sqlite3 &> /dev/null; then
    echo "错误: sqlite3 未安装。请先安装 sqlite3。"
    exit 1
fi

# 创建目录
mkdir -p "$BACKUP_DIR" "$SCHEMA_DIR" "$DATA_DIR"

echo -e "${YELLOW}开始备份数据库...${NC}"

# 1. 导出完整备份
echo "导出完整数据库备份..."
sqlite3 "$DB_PATH" ".dump" > "$BACKUP_DIR/full-backup.sql"

# 2. 导出数据库结构
echo "导出数据库结构..."
sqlite3 "$DB_PATH" ".schema" > "$SCHEMA_DIR/schema.sql"

# 3. 导出核心表数据
echo "导出用户和角色数据..."
sqlite3 "$DB_PATH" "SELECT 'INSERT INTO roles VALUES(' || quote(id) || ',' || quote(code) || ',' || quote(name) || ',' || quote(description) || ',' || quote(permissions) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM roles;" > "$DATA_DIR/roles.sql"

sqlite3 "$DB_PATH" "SELECT 'INSERT INTO users VALUES(' || quote(id) || ',' || quote(username) || ',' || quote(password) || ',' || quote(email) || ',' || quote(full_name) || ',' || quote(role_id) || ',' || quote(is_active) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM users;" > "$DATA_DIR/users.sql"

echo "导出设备数据..."
sqlite3 "$DB_PATH" "SELECT 'INSERT INTO devices VALUES(' || quote(id) || ',' || quote(device_id) || ',' || quote(name) || ',' || quote(specialty) || ',' || quote(classification_name) || ',' || quote(classification_code) || ',' || quote(naming_table_id) || ',' || quote(position_table_id) || ',' || quote(base_params_table_id) || ',' || quote(tech_spec_table_id) || ',' || quote(sequence_number) || ',' || quote(remarks) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM devices;" > "$DATA_DIR/devices.sql"

echo "导出参数元数据..."
sqlite3 "$DB_PATH" "SELECT 'INSERT INTO parameter_field_metadata VALUES(' || quote(id) || ',' || quote(table_name) || ',' || quote(table_code) || ',' || quote(table_description) || ',' || quote(field_sequence) || ',' || quote(field_name) || ',' || quote(field_column) || ',' || quote(example_description) || ',' || quote(data_type) || ',' || quote(measurement_class) || ',' || quote(data_source) || ',' || quote(is_required) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM parameter_field_metadata;" > "$DATA_DIR/parameter_field_metadata.sql"

sqlite3 "$DB_PATH" "SELECT 'INSERT INTO parameter_table_metadata VALUES(' || quote(id) || ',' || quote(table_name) || ',' || quote(table_code) || ',' || quote(description) || ',' || quote(source_file) || ',' || quote(created_at) || ');' FROM parameter_table_metadata;" > "$DATA_DIR/parameter_table_metadata.sql"

echo ""
echo -e "${GREEN}备份完成！${NC}"
echo ""

# 显示备份统计信息
echo -e "${BLUE}备份统计信息:${NC}"
echo "完整备份文件: $(du -h "$BACKUP_DIR/full-backup.sql" | cut -f1)"
echo "结构文件: $(du -h "$SCHEMA_DIR/schema.sql" | cut -f1)"
echo ""
echo "数据文件:"
for file in "$DATA_DIR"/*.sql; do
    if [ -f "$file" ]; then
        filename=$(basename "$file")
        size=$(du -h "$file" | cut -f1)
        lines=$(wc -l < "$file")
        echo "  $filename: $size ($lines 行)"
    fi
done

echo ""
echo -e "${GREEN}所有备份文件已生成完成！${NC}"